package cvs;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class UploadCSV {

	public static void main(String[] args) {

		System.out.println("start");

		// uploadNationalAccount();
		// uploadProduct();
		// uploadQuota();
		// uploadPrice();
		// uploadCFD();

		// accountType();

		// accountRep();
		// uploadProduct();

		// uploadExceptions();
		// uploadClaimExtra();
		// uploadBlankDuns();

		// uploadPrice();

		// uploadClaimException08();
		// uploadClaimExtra08();

		// uploadClaimExtra07();
		
		uploadNaAccount();
		System.out.println("end");
	}

	private static void uploadNaAccount() {
		String tableName = "t_claim_na_temp";
		String fileName = "/home/network/sales/na accounts/temp_etailesr.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}
	
	
	private static void uploadExceptions() {
		String tableName = "sales_comm.dbo.temp_exception";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\smi\\exception\\6\\6. Exception Uploading Form v2.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadClaimException08() {
		String tableName = "t_claim_exception";
		String fileName = "/home/network/sales/exception/8. Exception Uploading Form (Aug).csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadPrice() {
		String tableName = "price_upload";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\sales2\\price\\Disty Invoice Price_08 2010-upload_to_table.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadBlankDuns() {

		String tableName = "temp_blank_duns";
		String fileName = "D:\\upload\\duns_blank.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);

	}

	private static void uploadClaimException() {
		String tableName = "t_claim_exception";
		String fileName = "C:/Documents and Settings/Samsung.SAMSUNG-A8F/Desktop/sales2/07 data/07-2010-exception.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadClaimExtra() {
		String tableName = "t_claim_extra";
		String fileName = "C:/Documents and Settings/Samsung.SAMSUNG-A8F/Desktop/sales2/tier1/07-2010 final.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadClaimExtra08() {
		String tableName = "t_claim_extra";
		String fileName = "/home/network/sales/tier1/2010 08 All Teams Direct Uploading form_100610.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadClaimExtra07() {
		String tableName = "t_claim_extra";
		String fileName = "/home/network/sales/tier1/2010 07 All Teams Direct Uploading form_100610.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void accountRep() {
		String tableName = "temp_account_rep";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\epos3\\ced\\account_type\\Mapping Result v2.0.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void accountType() {
		String tableName = "temp_account_type";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\epos3\\ced\\account_type\\list.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadNationalAccount() {
		String tableName = "[sales_comm].[dbo].[national_account]";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\smi\\natioanl account\\National Account Update List_06 2010 072010.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadProduct() {
		String tableName = "temp_product_upload";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\smi\\price\\upload product\\ProductBulletin_June 2010___.csv";

		showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadCFD() {
		String tableName = "temp_cfd_for_upload";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\smi\\cfd\\DUNS_LIST(CSV).csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void uploadQuota() {
		String tableName = "sales_comm.dbo.temp_quota_for_upload";
		String fileName = "C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\smi\\quota\\All Sales Reps Quota_2010 JUN Uploading.csv";

		// showCreateTable(new File(fileName), tableName);
		loadFile(new File(fileName), tableName);
	}

	private static void showCreateTable(File newFile, String table) {
		FileReader reader = null;
		try {
			reader = new FileReader(newFile);

			DelimitedFileReader csvReader = new DelimitedFileReader(reader);
			String[] tokens = csvReader.readNext();

			String sql = "CREATE TABLE " + table + " ( ";
			for (int i = 0; i < tokens.length; i++) {
				sql = sql + tokens[i] + " varchar(255)";

				if (i < tokens.length - 1)
					sql = sql + ',';
			}
			sql = sql + " )";
			System.out.println(sql);

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null)
				try {
					reader.close();
				} catch (IOException e) {
				}
		}
	}

	public static void loadFile(File newFile, String table) {
		FileReader reader = null;
		try {
			reader = new FileReader(newFile);

			DelimitedFileReader csvReader = new DelimitedFileReader(reader);
			String[] tokens = csvReader.readNext();

			String sql = "insert into " + table + " ( ";
			for (int i = 0; i < tokens.length; i++) {
				sql = sql + tokens[i];

				if (i < tokens.length - 1)
					sql = sql + ',';
			}

			sql = sql + ") values( ";

			for (int i = 0; i < tokens.length; i++) {
				sql = sql + "?";

				if (i < tokens.length - 1)
					sql = sql + ',';
			}
			sql = sql + ");";

			System.out.println(sql);

			PreparedStatement ps;
			ResultSet rs;

			if (1 == 1) {
				System.out.println(sql);
				System.out.println();
				System.out.println();
				System.out.println();
			}
			DBConnectorReal connector = new DBConnectorReal();

			try {
				connector.start();
				Connection connection = connector.getConnection();

				ps = connection.prepareStatement(sql);

				while ((tokens = csvReader.readNext()) != null) {

					for (int i = 0; i < tokens.length; i++) {
						ps.setString(i + 1, tokens[i]);
					}
					ps.executeUpdate();
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				connector.close();
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (reader != null)
				try {
					reader.close();
				} catch (IOException e) {
				}
		}
	}
}
